﻿using KonyvKolcsonzes;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Konyvkolcsonzo
{
    public struct konyvkicsi
    {
        public long id;
        public string cim;
    }

    public struct konyv
    {
        public long id;
        public string cim;
        public int peldanyszam;
        public string irok;
        public string mufajok;
    }

    class Konyvek : DAL
    {
        public List<konyv> getKonyvek(string keresoBetu)
        {
            openConnection();
            string error = "";
            List<konyvkicsi> nevsor1 = new List<konyvkicsi>();
            List<konyv> nevsor = new List<konyv>();
            SqlDataReader reader = executeReader("select KonyvID, Cim from Konyvek where Cim like '%" + keresoBetu + "%'", ref error);

            while (reader.Read())
            {
                konyvkicsi l;
                l.id = Convert.ToInt32(reader["KonyvID"]);
                l.cim = Convert.ToString(reader["Cim"]);
                nevsor1.Add(l);
            }
            closeConnection();

            for (int i = 0; i < nevsor1.Count; i++)
            {
                konyv k;
                k.id = nevsor1[i].id;
                k.cim = nevsor1[i].cim;
                openConnection();
                SqlDataReader readerIro = executeReader("select IroNev from Irok, Iro  where Iro.IroID = Irok.IroID and KonyvId =" + k.id, ref error);
                string irok = "";// new StringBuilder();
                while (readerIro.Read())
                {
                    if (irok.Length > 0)
                    {
                        irok = irok + ", ";
                    }
                    irok = irok + Convert.ToString(readerIro["IroNev"]);
                }
                closeConnection();

                openConnection();
                SqlDataReader readerPeldanySzam = executeReader("select COUNT(Peldany.KonyvID) AS NR from Peldany where Peldany.KonyvID = " + k.id, ref error);
                int peldany = 0;
                while (readerPeldanySzam.Read())
                {
                    peldany = Convert.ToInt16(readerPeldanySzam["NR"]);
                }
                closeConnection();

                openConnection();
                SqlDataReader readerTipus = executeReader("select TipusNev from Tipusok, Tipus where Tipus.TipusID = Tipusok.TipusID and KonyvId =" + k.id, ref error);
                StringBuilder tipus = new StringBuilder();
                while (readerTipus.Read())
                {
                    if (tipus.Length > 0)
                    {
                        tipus = tipus.Append(", ");
                    }
                    tipus = tipus.Append(Convert.ToString(readerTipus["TipusNev"]));
                }
                closeConnection();

                k.peldanyszam = peldany;
                k.irok = irok.ToString();
                k.mufajok = tipus.ToString();
                nevsor.Add(k);
            }
            return nevsor;
        }

        public string getKonyv(long id){
            openConnection();
            string err = "";
            string cim = "";
            SqlDataReader read = executeReader("select Cim from Konyvek where KonyvID = " + id,ref err);
            while(reader.Read()){
                cim = Convert.ToString(reader["Cim"]);}
            closeConnection();
            return cim;
        }

        public List<string> getIrok()
        {
            openConnection();
            string error = "";
            List<string> nevsor = new List<string>();
            SqlDataReader reader = executeReader("select IroNev from Irok", ref error);
            while (reader.Read())
            {
                nevsor.Add(Convert.ToString(reader["IroNev"]));
            }
            closeConnection();
            return nevsor;
        }

        public List<string> getMufajok()
        {
            openConnection();
            string error = "";
            List<string> nevsor = new List<string>();
            SqlDataReader reader = executeReader("select TipusNev from Tipusok", ref error);
            while (reader.Read())
            {
                nevsor.Add(Convert.ToString(reader["TipusNev"]));
            }
            closeConnection();
            return nevsor;
        }

        public string updateKonyv(konyv k, List<string> iroi, List<string> mufajai)
        {
            deleteIroi(k.id);
            deleteMufajai(k.id);
            openConnection();
            //Konyv cim
            string error = executeUpdateCommand("update Konyvek set Cim = '" + k.cim + "' where KonyvID = " + k.id );
            closeConnection();
            long id = new long();

            //Irok
            for (int i = 0; i < iroi.Count; i++)
            {
                id = getIroID(iroi[i]);
                openConnection();
                error = executeUpdateCommand("insert into Iro (IroId, KonyvId) values ('" + id + "', '" + k.id + "')");
                closeConnection();
            }

            //Mufajok
            for (int i = 0; i < mufajai.Count; i++)
            {
                id = getMufajID(mufajai[i]);
                openConnection();
                error = executeUpdateCommand("insert into Tipus (TipusID, KonyvId) values ('" + id + "', '" + k.id + "')");
                closeConnection();
            }

            return error;
        }

        public string insertKonyvek(konyv k, List<string> iroi, List<string> mufajai)
        {
            //KonyvCim
            openConnection();
            string error = executeInsertCommand("INSERT into Konyvek (Cim) values  ('" + k.cim + "')");
            closeConnection();
            long p = new long();

            //Irok
            long id = new long();
            string err = "";
            openConnection();
            SqlDataReader reader = executeReader("select KonyvID from Konyvek where Cim like '" + k.cim + "'", ref err);
            while (reader.Read())
            {
                id = Convert.ToInt64(reader["KonyvID"]);
            }
            closeConnection();

            for (int i = 0; i < iroi.Count; i++)
            {
                p = getIroID(iroi[i]);
                openConnection();
                error = executeInsertCommand("INSERT into Iro (IroID, KonyvID) values ('" + p +"', '" + id + "')");
                closeConnection();
            }

            //Mufajok
            for (int i = 0; i < mufajai.Count; i++)
            {
                p = getMufajID(mufajai[i]);
                openConnection();
                error = executeInsertCommand("INSERT into Tipus (TipusID, KonyvID) values ('" + p + "', '" + id + "')");
                closeConnection();
            }

            return error;
        }

        public string deleteKonyv(long id)
        {
            deleteIroi(id);
            deleteMufajai(id);
            deleteKolcsonzesei(id);
            deletePeldanyai(id);
            openConnection();
            string error = executeDeleteCommand("delete from Konyvek where KonyvID = " + id);
            closeConnection();
            return error;
        }

        public void deleteIroi(long konyvID)
        {
            openConnection();
            string error = executeDeleteCommand("delete from Iro where KonyvID = " + konyvID);
            closeConnection();
        }

        public long getIroID(string iroNev)
        {
            string err = "";
            long id = -1;
            openConnection();
            SqlDataReader reader = executeReader("select IroID from Irok where IroNev like '" + iroNev + "'", ref err);
            while (reader.Read())
            {
                id = Convert.ToInt32(reader["IroID"]);
            }
            closeConnection();
            return id;
        }
        
        public long getMufajID(string mufajNev)
        {
            string err = "";
            long id = -1;
            openConnection();
            SqlDataReader reader = executeReader("select TipusID from Tipusok where TipusNev like '" + mufajNev + "'", ref err);
            while (reader.Read())
            {
                id = Convert.ToInt32(reader["TipusID"]);
            }
            closeConnection();
            return id;
        }

        public void deleteMufajai(long konyvID)
        {
            openConnection();
            string error = executeDeleteCommand("delete from Tipus where KonyvID = " + konyvID);
            closeConnection();
        }

        public void deletePeldanyai(long konyvID)
        {
            openConnection();
            string error = executeDeleteCommand("delete from Peldany where KonyvID = " + konyvID);
            closeConnection();
        }

        public void deleteKolcsonzesei(long konyvID)
        {
            string err = "";
            List<long> nevsor = new List<long>();
            openConnection();
            SqlDataReader reader = executeReader("select PeldanyID from Peldany where KonyvID = " + konyvID, ref err);
            while (reader.Read())
            {
                nevsor.Add(Convert.ToInt32(reader["PeldanyID"]));
            }
            closeConnection();

            for (int i = 0; i < nevsor.Count; i++)
            {
                openConnection();
                string error = executeDeleteCommand("delete from Kikolcsonozve where PeldanyID = " + nevsor[i]);
                closeConnection();
            }
        }
        
    }
}